/*
 * @Author: xzy 1453077527@qq.com
 * @Date: 2025-08-21 11:11:57
 * @LastEditors: xzy 1453077527@qq.com
 * @LastEditTime: 2025-08-27 09:50:58
 * @Description:
 */
package com.xtedu.examproject.dao.impl;

import ch.qos.logback.classic.spi.EventArgUtil;
import cn.hutool.crypto.digest.DigestUtil;
import com.xtedu.examproject.dao.base.RoleDetailInfoDao;
import com.xtedu.examproject.dao.base.UserDao;
import com.xtedu.examproject.entity.User;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao, RoleDetailInfoDao {
    @Override
    public int insert(User user) throws Exception {
        String sql = "insert into users(user_name,pwd,role_id,info_id) values(?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getName());
            ps.setString(2, DigestUtil.sha256Hex(user.getPassword()));
            ps.setInt(3, user.getRoleId());
            ps.setInt(4, user.getInfoId());
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public int update(User user) throws Exception {
        String sql = "update users set user_name=?,pwd=?,role_id=?,info_id=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getName());
            ps.setString(2, DigestUtil.sha256Hex(user.getPassword()));
            ps.setInt(3, user.getRoleId());
            ps.setInt(4, user.getInfoId());
            ps.setInt(5, user.getId());
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public int delete(User user) throws Exception {
        String sql = "delete from users where id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1, user.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        String sql = "delete from users where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)
        ) {
            pst.setInt(1, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public User findById(int id) throws Exception {
        String sql = "select * from users where id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
        ) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            User user = new User();
            while (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));
            }
            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> findAll() throws Exception {
        String sql = "select * from users";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));
                users.add(user);
            }
            return users;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        return null;
    }

    @Override
    public User findByUsername(String username) {
        String sql = "select * from users where user_name = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)
        ) {
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            User user = new User();
            if (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));
            }
            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> findByRoleId(int roleId) {
        String sql = "select * from users where role_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, roleId);
            ResultSet rs = ps.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));
                users.add(user);
            }
            return users;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public PageHelper findPageByRoleId(int roleId, int userId, int pageIndex, int pageSize, int roleSelect) {
        List<User> users = new ArrayList<>();

        // 如果查询角色为管理员
        if (roleId == 3) {
            String sql = "select * from users where role_id = ? limit ?,?";
            String sql1 = "select ceil(count(*) / ?) from users where role_id = ?";
            try (Connection conn = DBUtil.getConnection();
                 PreparedStatement pst = conn.prepareStatement(sql);
                 Connection conn1 = DBUtil.getConnection();
                 PreparedStatement pst1 = conn1.prepareStatement(sql1)) {
                // 填入参数
                pst.setInt(1, roleSelect);
                pst.setInt(2, (pageIndex - 1) * pageSize);
                pst.setInt(3, pageSize);
                ResultSet rs = pst.executeQuery();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("user_name"));
                    user.setPassword(rs.getString("pwd"));
                    user.setRoleId(rs.getInt("role_id"));
                    user.setInfoId(rs.getInt("info_id"));
                    user.setState(rs.getBoolean("state"));

                    users.add(user);
                }

                // 进行分页返回
                pst1.setInt(1, pageSize);
                // 设置参数
                pst1.setInt(2, roleSelect);

                ResultSet rs1 = pst1.executeQuery();
                int totalPages = 0;
                if (rs1.next()) {
                    totalPages = rs1.getInt(1);
                }
                return new PageHelper<>(users, pageIndex, pageSize, totalPages, "", pageIndex);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        // 如果查询角色为老师
        if (roleId == 1) {
            String sql = """
                    select u1.* from users u\s
                    left join teachers t on t.id = u.info_id
                    left join classes c on c.teacher_id = t.id
                    left join students s on s.class_id = c.id
                    left join users u1 on s.id = u1.info_id
                    where u.id = ? limit ?, ?
                    """;
            String sql1 = """
                    select ceil(count(*) / ?) from users u\s
                    left join teachers t on t.id = u.info_id
                    left join classes c on c.teacher_id = t.id
                    left join students s on s.class_id = c.id
                    left join users u1 on s.id = u1.info_id
                    where u.id = ?
                    """;

            try (
                    Connection conn = DBUtil.getConnection();
                    PreparedStatement pst = conn.prepareStatement(sql);
                    Connection conn1 = DBUtil.getConnection();
                    PreparedStatement pst1 = conn1.prepareStatement(sql1)
            ) {
                pst.setInt(1, userId);
                pst.setInt(2, (pageIndex - 1) * pageSize);
                pst.setInt(3, pageSize);
                ResultSet rs = pst.executeQuery();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("user_name"));
                    user.setPassword(rs.getString("pwd"));
                    user.setRoleId(rs.getInt("role_id"));
                    user.setInfoId(rs.getInt("info_id"));
                    user.setState(rs.getBoolean("state"));
                    users.add(user);
                }

                // 进行分页返回
                pst1.setInt(1, pageSize);
                // 设置参数
                pst1.setInt(2, userId);

                ResultSet rs1 = pst1.executeQuery();
                int totalPages = 0;
                if (rs1.next()) {
                    totalPages = rs1.getInt(1);
                }
                return new PageHelper<>(users, pageIndex, pageSize, totalPages, "", pageIndex);

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }


        }

        return null;
    }

    @Override
    public PageHelper findPageByRoleIds(int pageIndex, int pageSize, int... roleIds) {
        String sql = "select * from users where role_id in (?,?,?,?,?,?,?) limit ?,?";
        // 进行分页返回
        String sql1 = "select ceil(count(*) / ?) from users where role_id in (?,?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql);
             Connection conn1 = DBUtil.getConnection();
             PreparedStatement pst1 = conn1.prepareStatement(sql1)) {
            // 设置参数
            pst.setInt(1, roleIds[0]);
            pst.setInt(2, roleIds[1]);
            pst.setInt(3, roleIds[2]);
            pst.setInt(4, roleIds[3]);
            pst.setInt(5, roleIds[4]);
            pst.setInt(6, roleIds[5]);
            pst.setInt(7, roleIds[6]);

            pst.setInt(8, (pageIndex - 1) * pageSize);
            pst.setInt(9, pageSize);

            // 查询符合条件用户
            ResultSet rs = pst.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));

                users.add(user);
            }

            // 查询总页数
            pst1.setInt(1, pageSize);
            // 设置参数
            pst1.setInt(2, roleIds[0]);
            pst1.setInt(3, roleIds[1]);
            pst1.setInt(4, roleIds[2]);
            pst1.setInt(5, roleIds[3]);
            pst1.setInt(6, roleIds[4]);
            pst1.setInt(7, roleIds[5]);
            pst1.setInt(8, roleIds[6]);

            ResultSet rs1 = pst1.executeQuery();
            int totalPages = 0;
            if (rs1.next()) {
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(users, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Object showDetailInfo(int userId) {
        return null;
    }

    @Override
    public List<User> showByOwnClazz(int userId) {
        String sql = """
                select u1.* from users u
                left join teachers t on t.id = u.info_id
                left join classes c on c.teacher_id = t.id
                left join students s on s.class_id = c.id
                right join users u1 on s.id = u1.info_id
                where u.id = ?""";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {
            pst.setInt(1, userId);
            ResultSet rs = pst.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));
                users.add(user);
            }
            return users;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public PageHelper<User> showPageByOwnClazz(int userId, int pageIndex, int pageSize) {
        String sql = """
                select u1.* from users u
                left join teachers t on t.id = u.info_id
                left join classes c on c.teacher_id = t.id
                left join students s on s.class_id = c.id
                right join users u1 on s.id = u1.info_id
                where u.id = ? limit ?, ?""";

        List<User> users = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {
            pst.setInt(1, userId);
            pst.setInt(2, (pageIndex - 1) * pageSize);
            pst.setInt(3, pageSize);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("pwd"));
                user.setRoleId(rs.getInt("role_id"));
                user.setInfoId(rs.getInt("info_id"));
                user.setState(rs.getBoolean("state"));

                users.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 进行分页返回
        String sql1 = """
                select ceil(count(*) / ?) from users u
                left join teachers t on t.id = u.info_id
                left join classes c on c.teacher_id = t.id
                left join students s on s.class_id = c.id
                right join users u1 on s.id = u1.info_id
                where u.id = ?""";
        try (Connection conn1 = DBUtil.getConnection();
             PreparedStatement pst1 = conn1.prepareStatement(sql1)) {
            pst1.setInt(1, pageSize);
            pst1.setInt(2, userId);

            ResultSet rs1 = pst1.executeQuery();
            int totalPages = 0;
            if (rs1.next()) {
                totalPages = rs1.getInt(1);
            }
            return new PageHelper<>(users, pageIndex, pageSize, totalPages, "", pageIndex);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int updateUserState(int userId, int state) {
        // 数据库同步更新
        String sql = state == 1 ? "update users set state = 1 where id = ?" : "update users set state = 0 where id = ?";
        try (
                Connection conn = DBUtil.getConnection();
                PreparedStatement pst = conn.prepareStatement(sql)
        ) {
            pst.setInt(1, userId);
            return pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新用户角色
     *
     * @param userId 要修改的用户id
     * @param roleId 要修改的角色id
     * @return 修改的行数
     */
    @Override
    public int updateUserRole(int userId, int roleId) {
        String sql = "update users set role_id = ? where id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)
        ) {
            pst.setInt(1, roleId);
            pst.setInt(2, userId);
            return pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
